create or replace procedure show_open_cursors is
   the_user          varchar2(30);
   v_database        v$database.name%type;  
   num                integer; 
   more          boolean;
begin
   -- get your name and database
   -- requires grant from user sys as:
   -- grant select on v_$open_cursor to scott;
   -- grant select on v_$database to scott;
   SELECT  name, user INTO v_database, the_user FROM v$database;

   --Print out a header
   htp.htmlOpen;
   htp.headOpen;
   htp.print('<TITLE>Open Cursors</TITLE>');
   htp.print('<META HTTP-EQUIV="Refresh" CONTENT="1; '||
     'URL=/dpls/sample/show_open_cursors">');
   htp.headClose;
   
   -- get numbers
   select count(HASH_VALUE) into num from v$open_cursor;
   htp.print('<H3><font color="red">'||num||
     '</font> open cursors for <font color="blue">'|| the_user||'@'||v_database ||'</font>');
   htp.print('</H3>');
   select count(HASH_VALUE) into num from v$open_cursor where user_name = the_user;
   htp.print('('||num||' for '||the_user||')<BR><HR>');
  
   more := owa_util.tablePrint('v$open_cursor', 
                'BORDER=1 BGCOLOR="FFFFE0"',
                OWA_UTIL.HTML_TABLE, '*', ' ORDER BY USER_NAME, SID', 
                NULL, 0, NULL);
  
   htp.htmlClose;
exception
  WHEN others then
    htp.print('Show Open Cursors EXCEPTION: '||sqlerrm||'<BR> '||the_user||' </HTML>');
end show_open_cursors;
/
